#coding=utf-8
from sqlalchemy import Column, DateTime, Integer, Unicode, and_, asc, or_
from ext.model import DBSession, DeclarativeBase
from ext.model_base import DynamicMixin

__ALL__ = ['Domain', 'DomainExpired']

class Domain(DeclarativeBase, DynamicMixin):
    __tablename__ = 'domain'

    name = Column(Unicode(100))
    level = Column(Integer())

    creation_date = Column(DateTime())
    expiration_date = Column(DateTime())
    updated_date = Column(DateTime())
    status = Column(Unicode(100))
    registrar = Column(Unicode(100))
    emails = Column(Unicode(100))
    referral_url = Column(Unicode(100))
    name_servers = Column(Unicode(100))
    whois_server = Column(Unicode(100))
    backorder = Column(Unicode(100))
    namecom = Column(Integer())

    @classmethod
    def find_all(cls):
        return DBSession.query(Domain).filter(cls.active == 1).order_by(cls.expiration_date).all()

    @classmethod
    def scan_all(cls):
        return DBSession.query(cls).filter(cls.status!='Available').order_by(asc(cls.expiration_date)).all()

    @classmethod
    def list_all(cls, ** kw):
        records = []
        for i in [1, 0]:
            qset = DBSession.query(cls).filter(cls.namecom==i)
            if kw.get('appendix'):
                qset = qset.filter(cls.name.like('%%.%s' % kw.get('appendix')))
            if kw.get('level'):
                qset = qset.filter(cls.level == kw.get('level'))
            if kw.get('none'):
                if kw['none']:
                    qset = qset.filter(or_(cls.expiration_date == None, cls.backorder == None))
                else:
                    qset = qset.filter(and_(cls.expiration_date != None, cls.backorder != None))
            qset = qset.order_by(asc(cls.expiration_date)).all()
            avai_set = []
            results = []
            for i in qset:
                if kw.get('length') and len(i.name) < int(kw.get('length')):
                    continue
                else:
                    if i.expiration_date:
                        results.append(i)
                    else:
                        avai_set.append(i)
            results.extend(avai_set)
            records.extend(results)
        return records

    @classmethod
    def reset_namecom(cls, names):
        DBSession.execute("update domain set namecom=0, backorder='backorder' where namecom=1")
        if names:
            DBSession.execute('update domain set namecom=1, backorder="taken" where name in ("%s")' % '","'.join(names))
            return DBSession.query(cls).filter(cls.name.in_(names)).all()
        else:
            return DBSession.query(cls).all()

    @classmethod
    def find_blank_whois_domains(cls):
        return DBSession.query(cls).filter(and_(cls.expiration_date == None, cls.status == None)).order_by(asc(cls.expiration_date)).all()

    @classmethod
    def find_blank_namecom_domains(cls):
        return DBSession.query(cls).filter(cls.backorder == None).order_by(asc(cls.expiration_date)).all()

class DomainExpiredEmail(DeclarativeBase, DynamicMixin):
    __tablename__ = 'domain_expired_email'

    email = Column(Unicode(100))

    @classmethod
    def create(cls, emails):
        for i in emails.split("|"):
            i = i.strip()
            if i and i.find('@')>-1 and not DBSession.query(Domain).filter(Domain.emails.like('%%%s%%' % i)).first():
                print 'create expired email: %s' % i
                DBSession.add(cls( ** {'email':i}))

    @classmethod
    def find_emails(cls):
        results = []
        for i in cls.find_all():
            results.append(i.email)
        return results
